from app.db import database, recipes
from app.models import RecipeDBSchema, RecipeSchema


async def create(recipe: RecipeSchema) -> int:
    # Create a SQLAlchemy insert object expression query:
    query = recipes.insert().values(
        title=recipe.title,
        cooking_time=recipe.cooking_time,
        ingredients=recipe.ingredients,
        description=recipe.description,
    )
    # Executes the query and returns the generated ID:
    return await database.execute(query=query)


async def get(recipe_id: int):
    query = recipes.select().where(recipes.c.id == recipe_id)
    return await database.fetch_one(query=query)


async def get_all() -> list:
    query = recipes.select().order_by(
        recipes.c.view_count.desc(), recipes.c.cooking_time.asc()
    )
    return await database.fetch_all(query=query)


async def update(recipe_id: int, recipe: RecipeSchema) -> int:
    query = (
        recipes.update()
        .where(recipes.c.id == recipe_id)
        .values(
            title=recipe.title,
            description=recipe.description,
            cooking_time=recipe.cooking_time,
            ingredients=recipe.ingredients,
        )
        .returning(recipes.c.id)
    )
    return await database.execute(query=query)


async def update_view_count(recipe_id: int, view_count: int) -> int:
    query = (
        recipes.update()
        .where(recipes.c.id == recipe_id)
        .values(
            view_count=view_count,
        )
        .returning(recipes.c.id)
    )
    return await database.execute(query=query)


async def delete(recipe_id: int) -> RecipeDBSchema | None:
    query = recipes.delete().where(recipe_id == recipes.c.id)  # type: ignore[arg-type]
    return await database.execute(query=query)
